package dao.impl;

import bean.Course;
import dao.CourseDao;
import util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDaoImpl implements CourseDao {

    Connection con = null;

    Course courses = null;

    PreparedStatement ps = null;

    ResultSet rs = null;

    int row = 0;

    @Override
    public List<Course> getCourseAll() {
        List<Course> CoursesList = new ArrayList<Course>();
        try {
            con = JdbcUtil.getCon();
            String sql = "select * from course";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                courses = new Course();
                courses.setCno(rs.getString("Cno"));
                courses.setCname(rs.getString("Cname"));
                courses.setCpno(rs.getString("Cpno"));
                courses.setCredit(rs.getDouble("Credit"));
                CoursesList.add(courses);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return CoursesList;
    }

    @Override
    public Course getCourseById(String Con) {
        try {
            con = JdbcUtil.getCon();
            String sql = "select * from course where Cno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, Con);
            rs = ps.executeQuery();
            while (rs.next()){
                courses = new Course();
                courses.setCno(rs.getString("Cno"));
                courses.setCname(rs.getString("Cname"));
                courses.setCpno(rs.getString("Cpno"));
                courses.setCredit(rs.getDouble("Credit"));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return courses;
    }

    @Override
    public int addCourse(Course course) {
        try {
            con = JdbcUtil.getCon();
            String sql = "Insert into course values(?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, course.getCno());
            ps.setString(2, course.getCname());
            ps.setString(3, course.getCpno());
            ps.setString(4, String.valueOf(course.getCredit()));
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }

    @Override
    public int updateCourse(Course course) {
        try {
            con = JdbcUtil.getCon();
            String sql = "update course set Cname = ? , Cpno = ? , Credit = ? where Cno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, course.getCname());
            ps.setString(2, course.getCpno());
            ps.setString(3, String.valueOf(course.getCredit()));
            ps.setString(4, course.getCno());
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }

    @Override
    public int deleteCourse(String Con) {
        try {
            con = JdbcUtil.getCon();
            String sql = "DELETE FROM course where Cno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, Con);
            row = ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return row;
    }

    @Override
    public List<Course> getStudentsByName(String cname) {
        List<Course> CoursesList = new ArrayList<Course>();
        try {
            con = JdbcUtil.getCon();
            String sql = "select * from course where Cname like ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%"+cname+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                courses = new Course();
                courses.setCno(rs.getString("Cno"));
                courses.setCname(rs.getString("Cname"));
                courses.setCpno(rs.getString("Cpno"));
                courses.setCredit(rs.getDouble("Credit"));
                CoursesList.add(courses);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                JdbcUtil.close(rs,ps,con);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return CoursesList;
    }
}
